package com.example.xlxs.util;

import com.example.xlxs.bean.User;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDateUtil {


    public static void main(String[] args) {
        List<Map<String, String>> maps = readExcel("F:\\user.xlsx");
        List<User> list = new ArrayList<>();
        assert maps != null;
        maps.forEach(map -> {
            String username = map.get("用户名");
            int age = Integer.parseInt(map.get("年龄"));
            String sex = map.get("性别");
            String phone = map.get("手机号");
            User u = new User(username, age, phone, sex);
            list.add(u);
        });
        list.forEach(System.out::println);
    }

    public static List<Map<String, String>> readExcel(String filePath) {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new RuntimeException("文件不存在，请检查文件目录");
        }
        try {
            XSSFWorkbook sheets = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheetAt = sheets.getSheetAt(0);
            ArrayList<Map<String, String>> list = new ArrayList<>();
            XSSFRow titleRow = sheetAt.getRow(0); //第一行，默认为标题行
            for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                XSSFRow row = sheetAt.getRow(i);  // 顺序遍历每一行
                Map<String, String> map = new HashMap<>();
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    XSSFCell titleCell = titleRow.getCell(index);
                    XSSFCell cell = row.getCell(index);
                    cell.setCellType(CellType.STRING);
                    if (cell.getStringCellValue().equals("")) {
                        continue;
                    }
                    map.put(getString(titleCell), getString(cell));
                }
                if (map.isEmpty()) {
                    continue;
                }
                list.add(map);
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("io异常=" + e.getMessage());
        }
        return null;
    }


    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }


    @SuppressWarnings("deprecation")
    public <T> List<T> parseFromExcel(String path, int firstIndex, Class<T> aimClass) {
        List<T> result = new ArrayList<T>();
        try {
            FileInputStream fis = new FileInputStream(path);
            Workbook workbook = WorkbookFactory.create(fis);
            //对excel文档的第一页,即sheet1进行操作
            Sheet sheet = workbook.getSheetAt(0);
            int lastRaw = sheet.getLastRowNum();
            for (int i = firstIndex; i <= lastRaw; i++) {
                //第i行
                Row row = sheet.getRow(i);
                T parseObject = aimClass.newInstance();
                Field[] fields = aimClass.getDeclaredFields();
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    Class<?> type = field.getType();
                    //第j列
                    Cell cell = row.getCell(j);
                    if (cell == null)
                        continue;
                    //很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String cellContent = cell.getStringCellValue();
                    cellContent = "".equals(cellContent) ? "0" : cellContent;
                    if (type.equals(String.class)) {
                        field.set(parseObject, cellContent);
                    } else if (type.equals(char.class) || type.equals(Character.class)) {
                        field.set(parseObject, cellContent.charAt(0));
                    } else if (type.equals(int.class) || type.equals(Integer.class)) {
                        field.set(parseObject, Integer.parseInt(cellContent));
                    } else if (type.equals(long.class) || type.equals(Long.class)) {
                        field.set(parseObject, Long.parseLong(cellContent));
                    } else if (type.equals(float.class) || type.equals(Float.class)) {
                        field.set(parseObject, Float.parseFloat(cellContent));
                    } else if (type.equals(double.class) || type.equals(Double.class)) {
                        field.set(parseObject, Double.parseDouble(cellContent));
                    } else if (type.equals(short.class) || type.equals(Short.class)) {
                        field.set(parseObject, Short.parseShort(cellContent));
                    } else if (type.equals(byte.class) || type.equals(Byte.class)) {
                        field.set(parseObject, Byte.parseByte(cellContent));
                    } else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
                        field.set(parseObject, Boolean.parseBoolean(cellContent));
                    }
                }
                result.add(parseObject);
            }
            fis.close();
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(
                    "An error occured when parsing object from Excel. at " + this.getClass());
        }
        return result;
    }


    public static <T> void writeExcel(List<T> beans, String path) throws IOException {
        if (beans == null || beans.size() == 0)
            return;
        File file = new File(path);
        if(!file.exists()){
            boolean newFile = file.createNewFile();
            System.out.println("文件是否创建成功="+newFile);
        }
        Workbook workbook = new HSSFWorkbook();
        FileOutputStream fos = null;
        try {
            Sheet sheet = workbook.createSheet();
            createTitle(beans,sheet);
            for (int i = 1; i < beans.size()+1; ++i) {
                Row row = sheet.createRow(i);
                T bean = beans.get(i-1);
                Field[] fields = bean.getClass().getDeclaredFields();
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    field.setAccessible(true);
                    Cell cell = row.createCell(j);
                    //Date,Calender都可以 使用  +"" 操作转成字符串
                    cell.setCellValue(field.get(bean) + "");
                }
            }
            fos = new FileOutputStream(path);
            workbook.write(fos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                fos.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static <T> void createTitle(List<T> beans, Sheet sheet) {
        Row row = sheet.createRow(0);
        T bean = beans.get(0);
        Field[] fields = bean.getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            Cell cell = row.createCell(i);
            cell.setCellValue(field.getName());
        }

    }
}

class Test{

    public static void main(String[] args) throws IOException {
        User user = new User("张三",20,"1234","女");
        User user2 = new User("李四",21,"12345","男");
        List<User> list = new ArrayList<>();
        list.add(user);
        list.add(user2);
        ExcelDateUtil.writeExcel(list,"F:\\user9.xlsx");
    }
}